package com.example.prompt.utils;

import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.util.*;

public class ExcelProcessor {

    /**
     * 文件读取
     */
    public static List<Map<String, Object>> readExcel(File file) throws IOException {
        return readExcel(new FileInputStream(file));
    }


    public static List<Map<String, Object>> readExcel(InputStream is) throws IOException {
        try (Workbook workbook = WorkbookFactory.create(is)) {
            return processWorkbook(workbook);
        }
    }

    private static List<Map<String, Object>> processWorkbook(Workbook workbook) {
        // 使用第一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        List<Map<String, Object>> dataList = new ArrayList<>();

        if (sheet == null || sheet.getPhysicalNumberOfRows() == 0) {
            return dataList;
        }

        // 获取标题行
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            throw new IllegalArgumentException("Excel文件缺少标题行");
        }

        List<String> headers = new ArrayList<>();
        for (Cell cell : headerRow) {
            headers.add(cell.getStringCellValue().trim());
        }

        // 遍历数据行
        for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue; // 跳过空行
            }
            Map<String, Object> rowData = new HashMap<>();
            for (int j = 0; j < headers.size(); j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    rowData.put(headers.get(j), null);
                    continue;
                }

                Object value = getCellValue(cell, workbook);
                rowData.put(headers.get(j), value);
            }
            dataList.add(rowData);
        }
        return dataList;
    }

    private static Object getCellValue(Cell cell, Workbook workbook) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getLocalDateTimeCellValue();
                } else {
                    return cell.getNumericCellValue();
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
                CellValue evaluatedValue = evaluator.evaluate(cell);
                switch (evaluatedValue.getCellType()) {
                    case STRING:
                        return evaluatedValue.getStringValue();
                    case NUMERIC:
                        return evaluatedValue.getNumberValue();
                    case BOOLEAN:
                        return evaluatedValue.getBooleanValue();
                    case ERROR:
                        return evaluatedValue.getErrorValue();
                    default:
                        return cell.getCellFormula();
                }
            case BLANK:
                return null;
            default:
                return null;
        }
    }

    public static void main(String[] args) {
        try {
            File file = new File("example.xlsx");
            List<Map<String, Object>> data = readExcel(file);
            for (Map<String, Object> row : data) {
                System.out.println(row);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}